from flask import Flask, session, jsonify, render_template, request, url_for, redirect
import pymysql
import time

app = Flask(__name__)
app.secret_key = '123456'
db_config = {
    'host': 'localhost',
    'user': 'system',
    'password': 'machao',
    'db': 'Library',
    'charset': 'utf8mb4',
}


@app.route('/login', methods=['GET', 'POST'])
def login():
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        try:
            cursor.execute("SELECT * FROM reader WHERE username = %s AND password = %s", (username, password))
            results = cursor.fetchone()
            if results:
                session['logged_in'] = True
                session['username'] = username
                session['ID'] = results[0]
                return redirect(url_for('index'))
            else:
                session['logged_in'] = False
                return '用户名或密码错误'
        except pymysql.MySQLError as e:
            session['logged_in'] = False
            print(f"查询时出错: {e}")
        finally:
            db.close()
    return render_template('reader_login.html')

@app.route('/register',methods=['get','post'])
def register():
    username = request.form['register-user']
    password = request.form['psw']
    telephone = request.form['Telephone']
    type = request.form['type']
    sql = 'insert into reader(username,password,type,state,telephone) values (%s,%s,%s,0,%s)'
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    try:
        cursor.execute(sql, (username, password, type, telephone))
        db.commit()
        return jsonify({'success': True, 'message': '注册成功！'})

    except Exception as e:
        print(f"数据库错误: {e}")
        return jsonify({'success': False, 'message': '注册失败，请重试！'}), 500
    finally:
        db.close()

@app.route('/', methods=['GET', 'POST'])
def index():
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    sql1 = 'SELECT * FROM book;'
    cursor.execute(sql1)
    book_results = cursor.fetchall()
    sql2 = 'SELECT * FROM space;'
    cursor.execute(sql2)
    space_results = cursor.fetchall()
    sql3 = 'SELECT * FROM reference;'
    cursor.execute(sql3)
    reference_results = cursor.fetchall()
    return render_template('index.html',book_results= book_results,space_results= space_results,refr_results= reference_results )


@app.route('/book_results', methods=['get', 'post'])
def book_search():
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    name = request.form.get('book_name')
    sql1 = 'SELECT ID,book_name,author,Location,State FROM book WHERE book_name LIKE %s'
    sql2 = 'SELECT * FROM book;'
    patten = f'%{name}%'
    try:
        cursor.execute(sql1, patten)
        results = cursor.fetchall()
        if results:
            return render_template('search_results.html', book_results=results)
        else:
            cursor.execute(sql2)
            results = cursor.fetchall()
            return render_template('search_results.html', book_results=results)
    except pymysql.MySQLError as e:
        print(f"查询时出错: {e}")
    finally:
        db.close()

@app.route('/borrow', methods=['post'])
def borrow():
    reader_id=session['ID']
    data = request.json
    book_id = data.get('bookId')
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    sql = 'insert into borrow(reader_id,book_id,lend_time,return_time) values (%s,%s,%s,%s)'
    local_time = f'{time.localtime().tm_year}年{time.localtime().tm_mon}月{time.localtime().tm_mday}日'
    deadline = f'{time.localtime().tm_year}年{time.localtime().tm_mon+1}月{time.localtime().tm_mday}日'
    try:
        cursor.execute(sql, (reader_id,book_id,local_time,deadline))
        db.commit()
        return jsonify({'success': True, 'message': '借阅成功！'})

    except Exception as e:
        print(f"数据库错误: {e}")
        return jsonify({'success': False, 'message': '借阅失败，请重试！'}), 500
    finally:
        db.close()

@app.route('/return_book',methods=['get','post'])
def return_book():
    data = request.json
    print(data)
    reader_id = data.get('readerId')
    book_id = data.get('bookId')
    sql = 'DELETE FROM borrow WHERE reader_id = %s AND book_id = %s'
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    try:
        cursor.execute(sql, (reader_id, book_id))
        db.commit()
        return jsonify({'success': True, 'message': '还书成功！'})

    except Exception as e:
        print(f"数据库错误: {e}")
        return jsonify({'success': False, 'message': '还书失败，请重试！'}), 500
    finally:
        db.close()

@app.route('/person', methods=['post', 'get'])
def person():
    id = session['ID']
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    sql1 = 'SELECT * FROM reader WHERE ID = %s'
    cursor.execute(sql1, (id,))
    results = cursor.fetchone()
    sql2 = 'SELECT book.book_name, book.author, book.ID, borrow.lend_time, borrow.return_time FROM reader JOIN borrow ON reader.ID = borrow.reader_id JOIN book ON borrow.book_id = book.ID WHERE reader.ID = %s;'
    cursor.execute(sql2, (id,))
    prits = cursor.fetchall()
    sql3 = 'SELECT space_id,space_name,reserv_time,deadline FROM reservation WHERE reader_id = %s'
    cursor.execute(sql3,(id,))
    space_result = cursor.fetchall()
    return render_template('person_page.html', username=results[1], type=results[3], state=results[4],
                           telephone=results[5], results=prits,space_results=space_result)

@app.route('/space_results', methods=['get', 'post'])
def space_search():
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    name = request.form.get('space_name')
    sql1 = 'SELECT ID,space_name,location,state FROM space WHERE space_name LIKE %s'
    sql2 = 'SELECT * FROM space;'
    patten = f'%{name}%'
    try:
        cursor.execute(sql1, patten)
        results = cursor.fetchall()
        if results:
            return render_template('search_results.html', space_results=results)
        else:
            cursor.execute(sql2)
            results = cursor.fetchall()
            return render_template('search_results.html', space_results=results)
    except pymysql.MySQLError as e:
        print(f"查询时出错: {e}")
    finally:
        db.close()

@app.route('/reservation', methods=['post'])
def reservation():
    reader_id=session['ID']
    data = request.json
    space_id = data.get('spaceId')
    space_name = data.get('space_name')
    reserv_time = data.get('reserv_time')
    deadline = data.get('deadline')
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    print(space_id,reader_id,space_name,reserv_time,deadline)
    sql = 'insert into reservation(space_id,reader_id,space_name,reserv_time,deadline) values (%s,%s,%s,%s,%s)'
    try:
        cursor.execute(sql, (space_id,reader_id,space_name,reserv_time,deadline))
        db.commit()
        return jsonify({'success': True, 'message': '预约成功！'})

    except Exception as e:
        print(f"数据库错误: {e}")
        return jsonify({'success': False, 'message': '预约失败，请重试！'}), 500
    finally:
        db.close()

@app.route('/cancel_reser',methods=['post','get'])
def cancel_reser():
    data = request.json
    space_id = data.get('spaceId')
    reader_id = data.get('readerId')
    sql = 'delete from reservation where space_id = %s and reader_id = %s;'
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    try:
        cursor.execute(sql,(space_id,reader_id))
        db.commit()
        return jsonify({'success':True,'message':'取消成功！'})
    except Exception as e:
        print(f'数据库错误：{e}')
        return jsonify({'success':False,'message':'取消失败！'})
    finally:
        db.close()

@app.route('/refr_results',methods=['get','post'])
def refr_results():
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    name = request.form.get('space_name')
    sql1 = 'SELECT ID,refer_title,author,quote,publish,publish_time FROM reference WHERE refer_title LIKE %s'
    sql2 = 'SELECT * FROM reference;'
    patten = f'%{name}%'
    try:
        cursor.execute(sql1, patten)
        results = cursor.fetchall()
        if results:
            return render_template('search_results.html', refr_results=results)
        else:
            cursor.execute(sql2)
            results = cursor.fetchall()
            return render_template('search_results.html', refr_results=results)
    except pymysql.MySQLError as e:
        print(f"查询时出错: {e}")
    finally:
        db.close()

@app.route('/scan',methods=['post','get'])
def scan():
    data = request.json
    refr_id = data.get('refrId')
    sql = 'update reference set quote = quote + 1 where ID = %s'
    db = pymysql.connect(**db_config)
    cursor = db.cursor()
    try:
        cursor.execute(sql,(refr_id,))
        db.commit()
        return jsonify({'success':True,'message':'查阅成功！'})
    except Exception as e:
        print(f'数据库错误：{e}')
        return jsonify({'success':False,'message':'查阅失败！'})
    finally:
        db.close()

@app.route('/admin',methods=['post','get'])
def admin():
    return render_template('admin_login.html')


if __name__ == '__main__':
    app.run(
        debug=True,
        port=8000
    )
